Excel VBA Tips: Stop Users from Adding Sheets

Excel VBA Tips: Stop Users from Adding Sheets

Do you have a spreadsheet that you want to stop users from adding sheets to? Of course, you could lock the workbook using the “protect workbook” feature and ensuring that the protect structure is selected.

However, say that you need to protect the workbook via vba. Place the code into “ThisWorkbook” module to allow you to stop users from adding worksheets. I found this code here.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    Sh.Delete
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
     MsgBox "Adding Sheets isn't allowed"
End Sub

Hope this is useful!

Come check out the directory for the rest of my excel tips!

Submeg

IT